﻿using System;
using System.Linq;
using System.Data.SQLite;
using Bruce.Data.Linq;
using System.Diagnostics;
using System.Linq.Expressions;

namespace Linq2SqliteTest
{
    class Program
    {
        const string connectionString = @"Data Source=E:\solutions\simple--cms\WebBuilder\WebSite\App_Data\data.db3;FailIfMissing=false;";

        static void Main(string[] args)
        {
            //LINQ to sqlite
            /*
             * 缺少状态跟踪
             * 缺少并发冲突检测机制
             */
            TestInsert();
            TestUpdate();
            TestSelect();
            TestDelete();


            /*
             * Entity framework不足:
             * 不支持数据库自动生成主键，此类数据只能程序分配
             * 需要.net 3.5 sp1支持
             * 删除和修改操作灵活性差，先查数据再操作，增加IO
             */
        }

        static void TestInsert()
        {
            using (SqliteDataContext db = new SqliteDataContext(new SQLiteConnection(connectionString)))
            {
                for (int i = 1; i < 101; i++)
                {
                    string test = i.ToString();
                    Admin admin = new Admin { AdminName = test, AdminPwd = (test + 1), };
                    db.Admin.InsertOnSubmit(admin);
                }

                db.SubmitChanges();
            }
        }

        static void TestUpdate()
        {
            using (SqliteDataContext db = new SqliteDataContext(new SQLiteConnection(connectionString)))
            {
                Expression<Func<Admin, string>> expressionName = (a => a.AdminName);
                Expression<Func<Admin, string>> expressionPwd = (a => a.AdminPwd);
                db.Update<Admin>(new Admin { AdminName = "newName", AdminPwd = "newPwd", },
                                    a => a.ID < 10,
                                    expressionName, expressionPwd);
            }
        }

        static void TestSelect()
        {
            using (SqliteDataContext db = new SqliteDataContext(new SQLiteConnection(connectionString)))
            {
                Console.WriteLine("The aministrators 10 < id < 20:");
                var query = from a in db.Admin
                            where a.ID < 20 && a.ID > 10
                            orderby a.AdminName ascending, a.AdminPwd descending
                            select a;

                /*
                 * 不支持group by查询，聚合函数难以表示
                 * select columnA,columnB from table where id > 100 and time < now()  order by id limit 5,10
                 */
                foreach (Admin item in query)
                {
                    Console.WriteLine("{0}/{1}/{2}", item.ID, item.AdminName, item.AdminPwd);
                }
            }
        }

        static void TestDelete()
        {
            using (SqliteDataContext db = new SqliteDataContext(new SQLiteConnection(connectionString)))
            {
                db.Delete<Admin>(a => true);
            }
        }
    }
}